/************************************************************************************/
/*  Program name CAPPOEXT_RECEIPT_LINE.sql                                          */
/*  Creater:  Kenny, Leung (PCCW - UniHub)                                         */
/*************   History   *************************                                */
/* 19 Jan 2006, Kenny Leung         First created                               */
/************************************************************************************/
ALTER SESSION SET NLS_DATE_FORMAT='YYYYMMDD HH24:MI:SS';
SET SERVEROUTPUT ON SIZE 1000000;

DECLARE
	lv_Prog_ID		VARCHAR2(100)	:=	'CAPPOEXT_RECEIPT_LINE';

	lv_Scc_Count	NUMBER	:=	0;
	lv_Err_Count	NUMBER	:=	0;
	lv_Err_Msg		VARCHAR2(500)	:=	'No Error';

	lv_FileName		VARCHAR2(100)	:=	'CAPPO_RECEIPT_LINE.dat';
	lv_PathName		VARCHAR2(100)	:=	'/tmp';
	lv_UTL_File		UTL_FILE.File_Type;

	lv_NGO_Name		VARCHAR2(100);
	lv_NGO_Count	NUMBER;

	CURSOR Cur_F(p_NGO VARCHAR2) IS
	SELECT RSL.* 
    FROM   RCV_SHIPMENT_LINES RSL,
       HR_ORGANIZATION_UNITS HOU
    WHERE  RSL.TO_ORGANIZATION_ID = HOU.ORGANIZATION_ID
    AND    HOU.NAME LIKE 'IO_'||p_NGO||'%';

BEGIN
	lv_NGO_Name		:=	SUBSTR(UPPER('&1'), -4);

	SELECT	COUNT('A')
	INTO	lv_NGO_Count
	FROM	HR_ORGANIZATION_UNITS
	WHERE	SUBSTR(Name,4,4)	=	lv_NGO_Name;

	IF	lv_NGO_Count	>=	1	THEN
        
        lv_UTL_File		:=	UTL_FILE.FOpen(lv_PathName, lv_FileName, 'w');
        
		FOR C1 IN Cur_F(lv_NGO_Name) LOOP
			
			UTL_FILE.PUT_LINE(
                  lv_UTL_File,
                  C1.SHIPMENT_LINE_ID              ||CHR(9)||
                  C1.LAST_UPDATE_DATE              ||CHR(9)||
                  C1.LAST_UPDATED_BY               ||CHR(9)||
                  C1.CREATION_DATE                 ||CHR(9)||
                  C1.CREATED_BY                    ||CHR(9)||
                  C1.LAST_UPDATE_LOGIN             ||CHR(9)||
                  C1.SHIPMENT_HEADER_ID            ||CHR(9)||
                  C1.LINE_NUM                      ||CHR(9)||
                  C1.CATEGORY_ID                   ||CHR(9)||
                  C1.QUANTITY_SHIPPED              ||CHR(9)||
                  C1.QUANTITY_RECEIVED             ||CHR(9)||
                  C1.UNIT_OF_MEASURE               ||CHR(9)||
                  C1.ITEM_DESCRIPTION              ||CHR(9)||
                  C1.ITEM_ID                       ||CHR(9)||
                  C1.ITEM_REVISION                 ||CHR(9)||
                  C1.VENDOR_ITEM_NUM               ||CHR(9)||
                  C1.VENDOR_LOT_NUM                ||CHR(9)||
                  C1.UOM_CONVERSION_RATE           ||CHR(9)||
                  C1.SHIPMENT_LINE_STATUS_CODE     ||CHR(9)||
                  C1.SOURCE_DOCUMENT_CODE          ||CHR(9)||
                  C1.PO_HEADER_ID                  ||CHR(9)||
                  C1.PO_RELEASE_ID                 ||CHR(9)||
                  C1.PO_LINE_ID                    ||CHR(9)||
                  C1.PO_LINE_LOCATION_ID           ||CHR(9)||
                  C1.PO_DISTRIBUTION_ID            ||CHR(9)||
                  C1.REQUISITION_LINE_ID           ||CHR(9)||
                  C1.REQ_DISTRIBUTION_ID           ||CHR(9)||
                  C1.ROUTING_HEADER_ID             ||CHR(9)||
                  C1.PACKING_SLIP                  ||CHR(9)||
                  C1.FROM_ORGANIZATION_ID          ||CHR(9)||
                  C1.DELIVER_TO_PERSON_ID          ||CHR(9)||
                  C1.EMPLOYEE_ID                   ||CHR(9)||
                  C1.DESTINATION_TYPE_CODE         ||CHR(9)||
                  C1.TO_ORGANIZATION_ID            ||CHR(9)||
                  C1.TO_SUBINVENTORY               ||CHR(9)||
                  C1.LOCATOR_ID                    ||CHR(9)||
                  C1.DELIVER_TO_LOCATION_ID        ||CHR(9)||
                  C1.CHARGE_ACCOUNT_ID             ||CHR(9)||
                  C1.TRANSPORTATION_ACCOUNT_ID     ||CHR(9)||
                  C1.SHIPMENT_UNIT_PRICE           ||CHR(9)||
                  C1.TRANSFER_COST                 ||CHR(9)||
                  C1.TRANSPORTATION_COST           ||CHR(9)||
                  C1.COMMENTS                      ||CHR(9)||
                  C1.ATTRIBUTE_CATEGORY            ||CHR(9)||
                  C1.ATTRIBUTE1                    ||CHR(9)||
                  C1.ATTRIBUTE2                    ||CHR(9)||
                  C1.ATTRIBUTE3                    ||CHR(9)||
                  C1.ATTRIBUTE4                    ||CHR(9)||
                  C1.ATTRIBUTE5                    ||CHR(9)||
                  C1.ATTRIBUTE6                    ||CHR(9)||
                  C1.ATTRIBUTE7                    ||CHR(9)||
                  C1.ATTRIBUTE8                    ||CHR(9)||
                  C1.ATTRIBUTE9                    ||CHR(9)||
                  C1.ATTRIBUTE10                   ||CHR(9)||
                  C1.ATTRIBUTE11                   ||CHR(9)||
                  C1.ATTRIBUTE12                   ||CHR(9)||
                  C1.ATTRIBUTE13                   ||CHR(9)||
                  C1.ATTRIBUTE14                   ||CHR(9)||
                  C1.ATTRIBUTE15                   ||CHR(9)||
                  C1.REASON_ID                     ||CHR(9)||
                  C1.USSGL_TRANSACTION_CODE        ||CHR(9)||
                  C1.GOVERNMENT_CONTEXT            ||CHR(9)||
                  C1.REQUEST_ID                    ||CHR(9)||
                  C1.PROGRAM_APPLICATION_ID        ||CHR(9)||
                  C1.PROGRAM_ID                    ||CHR(9)||
                  C1.PROGRAM_UPDATE_DATE           ||CHR(9)||
                  C1.DESTINATION_CONTEXT           ||CHR(9)||
                  C1.PRIMARY_UNIT_OF_MEASURE       ||CHR(9)||
                  C1.EXCESS_TRANSPORT_REASON       ||CHR(9)||
                  C1.EXCESS_TRANSPORT_RESPONSIBLE  ||CHR(9)||
                  C1.EXCESS_TRANSPORT_AUTH_NUM     ||CHR(9)||
                  C1.ASN_LINE_FLAG                 ||CHR(9)||
                  C1.ORIGINAL_ASN_PARENT_LINE_ID   ||CHR(9)||
                  C1.ORIGINAL_ASN_LINE_FLAG        ||CHR(9)||
                  C1.VENDOR_CUM_SHIPPED_QUANTITY   ||CHR(9)||
                  C1.NOTICE_UNIT_PRICE             ||CHR(9)||
                  C1.TAX_NAME                      ||CHR(9)||
                  C1.TAX_AMOUNT                    ||CHR(9)||
                  C1.INVOICE_STATUS_CODE           ||CHR(9)||
                  C1.CUM_COMPARISON_FLAG           ||CHR(9)||
                  C1.CONTAINER_NUM                 ||CHR(9)||
                  C1.TRUCK_NUM                     ||CHR(9)||
                  C1.BAR_CODE_LABEL                ||CHR(9)||
                  C1.TRANSFER_PERCENTAGE           ||CHR(9)||
                  C1.MRC_SHIPMENT_UNIT_PRICE       ||CHR(9)||
                  C1.MRC_TRANSFER_COST             ||CHR(9)||
                  C1.MRC_TRANSPORTATION_COST       ||CHR(9)||
                  C1.MRC_NOTICE_UNIT_PRICE         ||CHR(9)||
                  C1.SHIP_TO_LOCATION_ID           ||CHR(9)||
                  C1.COUNTRY_OF_ORIGIN_CODE        ||CHR(9)||
                  C1.OE_ORDER_HEADER_ID            ||CHR(9)||
                  C1.OE_ORDER_LINE_ID              ||CHR(9)||
                  C1.CUSTOMER_ITEM_NUM             ||CHR(9)||
                  C1.COST_GROUP_ID                 ||CHR(9)||
                  C1.SECONDARY_QUANTITY_SHIPPED    ||CHR(9)||
                  C1.SECONDARY_QUANTITY_RECEIVED   ||CHR(9)||
                  C1.SECONDARY_UNIT_OF_MEASURE     ||CHR(9)||
                  C1.QC_GRADE                     
			);

			lv_Scc_Count	:=	lv_Scc_Count	+	1;
		END LOOP;
		UTL_FILE.FClose(lv_UTL_File);
	ELSE
		lv_Err_Count	:=	lv_Err_Count + 1;
		lv_Err_Msg		:=	lv_NGO_Name||': No RCV lines for NGO'; /*Change it for proper error message*/
	END IF;

	INSERT INTO CAPCNV_LOG(
		PROG_ID,
		PROCESS_DATE,
		RECORD_PROCESSED,
		RECORD_REJECTED,
		RECORD_EXTRACTED,
		ERR_MSG
	) VALUES (
		lv_Prog_ID,
		SYSDATE,
		lv_Scc_Count,
		lv_Err_Count,
		lv_Scc_Count-lv_Err_Count,
		lv_Err_Msg
	);

	COMMIT;
END;
/

